package common.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.context.annotation.Scope;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.dao.JdbcDAO;

import common.pojo.RoomStyle;


@Scope("prototype")
@Repository
public class RoomStyleDAO extends JdbcDAO {

	/**	
	 * 分页查询
	 * @param start
	 * @param limit
	 * @param orderby
	 * @param desc
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<RoomStyle> findList(int start, int limit,String orderbys, String where){
	
		class RowMapper implements org.springframework.jdbc.core.RowMapper {
			public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
				RoomStyle roomStyle = new RoomStyle();
				roomStyle.setId(rs.getInt(1));
				roomStyle.setName(rs.getString(2));
				roomStyle.setPrice(rs.getDouble(3));
				roomStyle.setBedType(rs.getInt(4));
				roomStyle.setPicUrl(rs.getString(5));
				roomStyle.setRoomNum(rs.getInt(6));
				roomStyle.setSort(rs.getInt(7));
				return roomStyle;
			}
		}
		String sql ="SELECT id,name,price,bedType,picUrl,roomNum,sort FROM "+ 
					"(SELECT *, ROW_NUMBER() OVER (ORDER BY "+orderbys+") as rowNum "+
					"FROM roomStyle where "+where +") AS T "+ 
					"WHERE T.RowNum>"+start+ "and T.RowNum<="+(start+limit)+"order by rowNum asc";
		return getList(sql, null, new RowMapper());
	}
	
	@SuppressWarnings("unchecked")
	public List<RoomStyle> findList(String where){
		
		String sql ="SELECT * from roomStyle   where "+where;
		return getList(sql, null, new RoomStyleMapper());
	}
	
	/**
	 * 取得数据总数
	 * @return
	 */	
	public int getCount(String where){
		return getCount("select count(*) from roomStyle where "+where,null);
	}

	
	/**
	 * 根据id操作
	 * @param id
	 * @return
	 */
	public RoomStyle findInfoById(int id){
		String sql ="select * from roomStyle where id ="+id;
		return (RoomStyle) getObject(sql,null, new RoomStyleMapper());
	}

	/**
	 * 所有房间最小价格
	 * @return
	 */
	public Double findMinPrice(){
		String sql = "select min(price) from roomStyle ";
		return (Double)getObject(sql,null,Double.class);
	}
	/**
	 * 更新数据库
	 * @param RoomStyle
	 * @return
	 */
	public void update(RoomStyle roomStyle){
		save("update roomStyle "
				+" set name=?,price=?,bedType=?,"+
				"breakfast=?,broadband=?,picUrl=?,remark=?,roomNum=? ,sort=? where id=?",
				new Object[]{roomStyle.getName(),roomStyle.getPrice(),roomStyle.getBedType(),roomStyle.getBreakfast(),roomStyle.getBroadband(),roomStyle.getPicUrl()
				,roomStyle.getRemark(),roomStyle.getRoomNum(),roomStyle.getSort(),roomStyle.getId()});
	}

	/**
	 * 删除
	 * @param id
	 * @return
	 */ 		
	public boolean delete(int id){
		String sql ="delete from roomStyle where id = "+id;
		delete(sql);
		return true;
	}
	

	
	public void save(RoomStyle roomStyle){
		String sql="insert into roomStyle values(?,?,?,?,?,?,?,?,?,?)";
		save(sql, new Object[] {roomStyle.getName(),roomStyle.getPrice(),roomStyle.getBedType(),roomStyle.getBreakfast(),roomStyle.getBroadband(),roomStyle.getPicUrl()
				,roomStyle.getRemark(),roomStyle.getRoomNum(),roomStyle.getSort(),roomStyle.getIsDelete()});
	}
	/**
	 * 映射私有类
	 * @author 
	 *
	 */
	private static final class RoomStyleMapper implements RowMapper {

		public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
			RoomStyle roomStyle = new RoomStyle();
			roomStyle.setId(rs.getInt(1));
			roomStyle.setName(rs.getString(2));
			roomStyle.setPrice(rs.getDouble(3));
			roomStyle.setBedType(rs.getInt(4));
			roomStyle.setBreakfast(rs.getShort(5));
			roomStyle.setBroadband(rs.getShort(6));
			roomStyle.setPicUrl(rs.getString(7));
			roomStyle.setRemark(rs.getString(8));
			roomStyle.setRoomNum(rs.getInt(9));
			roomStyle.setSort(rs.getInt(10));
			roomStyle.setIsDelete(rs.getBoolean(11));
			return roomStyle;
		}
	}
	

}

